package 实验九;

import javax.swing.JFrame;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;


public class personselect extends JFrame {

    private JTable table;
    private MyTableModel tablemodel;
    private JButton b1;
    private JButton b2;
    private JButton b3;
    private JToolBar tool;

    private JButton b4;
    private JTextField dept;

    public personselect() {
        this.setSize(600,300);
        this.setTitle("员工信息");
        this.setLocationRelativeTo(getOwner());//居中


        //获取表格模型(数据),创建组件JTable
        tablemodel = getModel();
        table = new JTable(tablemodel); //table = new JTable(_______);
        table.setPreferredScrollableViewportSize(new Dimension(500,250));
        JScrollPane scroll = new JScrollPane(table);
        getContentPane().add(scroll,BorderLayout.CENTER);

        b1 = new JButton(new ImageIcon("添加"));
        b1.setToolTipText("添加");
        b1.setFocusable(false);
        b1.setHorizontalTextPosition(SwingConstants.CENTER);
        b1.setVerticalTextPosition(SwingConstants.BOTTOM);
        b2 = new JButton();
        b2.setIcon(new ImageIcon("ico\\UPDATE.GIF"));
        b2.setToolTipText("修改");
        b2.setFocusable(false);
        b2.setHorizontalTextPosition(SwingConstants.CENTER);
        b2.setVerticalTextPosition(SwingConstants.BOTTOM);
        b3 = new JButton(new ImageIcon("ico\\DELETE.GIF"));
        b3.setToolTipText("删除");
        b3.setFocusable(false);
        b3.setHorizontalTextPosition(SwingConstants.CENTER);
        b3.setVerticalTextPosition(SwingConstants.BOTTOM);

        //创建工具栏，添加按钮
        tool = new JToolBar();
        tool.add(b1);
        tool.add(b2);
        tool.add(b3);
        tool.setRollover(true);

        //添加工具栏
        getContentPane().add(tool,BorderLayout.NORTH);
        b1.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                personadd padd = new personadd();//添加数据
                padd.setVisible(true);
                dispose();
            }
        });
        b2.addActionListener(new ActionListener(){
            public void actionPerformed(ActionEvent e) {
                int i,index = 0,count;
                db dbcon = new db();
                if(table.getCellEditor() != null) {
                    table.getCellEditor().stopCellEditing();
                }
                try {
                    String sql = "update person set name = ?,sex = ?,birthday = ?,professsor = ?,deptno = ?, notes = ? where no = ?";
                    //这里更改的时候加上一个notes
                    PreparedStatement presta = dbcon.PreparedStatement(sql);
                    //获得JTable中所修改的行数
                    count = tablemodel.getEditedIndex().size();
                    //获得JTable中所修改的行的数据，更新数据库
                    if(count > 0 ) {
                        for(i=0;i<count;i++) {
                            index = tablemodel.getEditedIndex().get(i);
                            presta.setString(1,(String)table.getValueAt(index, 1));
                            presta.setString(2,(String)table.getValueAt(index, 2));
                            String temp = String.valueOf(table.getValueAt(index, 3));
                            presta.setString(3,temp.equals("null")?null:temp);
                            presta.setString(4,(String)table.getValueAt(index, 4));
                            presta.setString(5,(String)table.getValueAt(index, 5));
                            presta.setString(6,(String)table.getValueAt(index, 0));
                            presta.setString(7,(String)table.getValueAt(index, 6));
                            presta.addBatch();
                        }
                    }
                    presta.executeBatch();
                    tablemodel.clearEditedIndex();

                }
                catch(SQLException sqle) {
                    System.out.println(sqle.toString());
                }
            }
        });
        b3.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                db dbcon = new db();
                try {
                    if(table.getSelectedRows().length>0) {
                        //获得JTable中选中行的序列
                        int[] selRowIndexs = table.getSelectedRows();
                        PreparedStatement presta = dbcon.PreparedStatement("delete from person where no = ?");
                        for(int i = 0;i<selRowIndexs.length;i++) {
                            presta.setString(1, table.getValueAt(selRowIndexs[i],0).toString());//删除无需修改，是按照no来删除的
                            presta.executeUpdate();
                        }

                        //重新加载数据到JTable
                        tablemodel = getModel();
                        table.setModel(tablemodel);
                    }
                }
                catch(SQLException sqle) {
                    System.out.println(sqle.toString());
                }
            }
        });
        b4 = new JButton(new ImageIcon("查询"));
        b4.setToolTipText("查询");
        b4.setFocusable(false);
        b4.setHorizontalTextPosition(SwingConstants.CENTER);
        b4.setVerticalTextPosition(SwingConstants.BOTTOM);

        dept = new JTextField();
        tool.add(b4);
        tool.add(dept);

        b4.addActionListener(new ActionListener() {

            public void actionPerformed(ActionEvent e) {
                db dbcon;
                try{
                    dbcon = new db();
                    String sql = "select * from person where deptno = ?";
                    PreparedStatement preparedStatement = dbcon.PreparedStatement(sql);
                    preparedStatement.setString(1,dept.getText());
                    ResultSet rs = preparedStatement.executeQuery();
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int Colnum = rsmd.getColumnCount();
                    tablemodel.setRowCount(0);
                    int i;

                    ArrayList<PersonEntity> v = new ArrayList<>();
                    while(rs.next()){
                        PersonEntity person = new PersonEntity();
                        person.setNo(rs.getString("no"));
                        person.setName(rs.getString("name"));
                        person.setSex(rs.getString("sex"));
                        person.setBirthday(rs.getDate("birthday"));
                        person.setProfessor(rs.getString("professsor"));
                        person.setDeptno(rs.getString("deptno"));

                        person.setNotes(rs.getString("notes"));//查询的时候增加一个列

                        v.add(person);
                    }
                    rs.close();
                    for(i=0;i<v.size();i++){
                        tablemodel.addRow(new Object[]{v.get(i).getNo(),v.get(i).getName(),v.get(i).getSex(),
                                v.get(i).getBirthday(),v.get(i).getProfessor(),v.get(i).getDeptno(),v.get(i).getNotes()});
                    }
                    dbcon.closeConn();
                }catch (Exception ex){
                    System.out.println(ex.getMessage());
                }
            }
        });
    }

    private MyTableModel getModel(){
        //实例化自己的表格模型类
        MyTableModel tableModel = new MyTableModel();

        db dbcon;
        try {
            //连接数据库，执行查询语句
            dbcon = new db();
            ResultSet rs = dbcon.executeQuery("select * from person");

            //获取查询结果中的列名，填充表格模型列
            ResultSetMetaData rsmd = rs.getMetaData();
            int Colnum = rsmd.getColumnCount();
            int i;
            for(i=1;i<=Colnum;i++)
                tableModel.addColumn(rsmd.getColumnName(i));

            //获取查询结果中的额元祖，填充表格模型行
            ArrayList<PersonEntity> v = new ArrayList<PersonEntity>();
            while(rs.next()) {
                PersonEntity person = new PersonEntity();
                person.setNo(rs.getString("no"));
                person.setName(rs.getString("name"));
                person.setSex(rs.getString("sex"));
                person.setBirthday(rs.getDate("birthday"));
                person.setProfessor(rs.getString("professsor"));
                person.setDeptno(rs.getString("deptno"));

                person.setNotes(rs.getString("notes"));//这里展示的时候增加一个列

                v.add(person);
            }
            rs.close();
            for(i=0;i<v.size();i++) {
                tableModel.addRow(new Object[] {v.get(i).getNo(),
                        v.get(i).getName(),v.get(i).getSex(),v.get(i).getBirthday(),v.get(i).getProfessor(),v.get(i).getDeptno(),
                        v.get(i).getNotes()
                });
            }
            dbcon.closeConn();
        }
        catch(SQLException sqle) {
            System.out.println(sqle.toString());
        }
        catch(Exception e) {
            System.out.println(e.getMessage());}

        return tableModel;
    }

    public static void main(String[] args) {
        personselect w = new personselect();
        w.setVisible(true);
    }
}
